package HP.com.bank_2.wangyu.dao;

import HP.com.bank_2.wangyu.bean.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class BankDao {

    /**
     * 通过查询卡号返回密码
     */
    public int selectPwd(int cardId) {
        int pwd = -1;

        return pwd;
    }

    public void orclTest(int userId) {
        String userIdStr = (new Integer(userId)).toString();
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:orcl"; // 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = "hp"; // 用户名,系统默认的账户名
            String password = "123456"; // 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password); // 获取连接
            System.out.println("连接成功！");
            //String sql = "select * from student where name=?"; // 预编译语句，“？”代表参数
            //String sql = "select * from BANKUSER";
            String sql = "select * from BANKUSER WHERE user_cardid=?";
            //String sql = "select * from qq WHERE user_cardid=?"; // 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql); // 实例化预编译语句
            pre.setString(1, userIdStr); // 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            result = pre.executeQuery(); // 执行查询，注意括号中不需要再加参数
            while (result.next())
                // 当结果集不为空时
                //System.out.println("学号:" + result.getInt("id") + "姓名:" + result.getString("name"));
                //System.out.println("result = " + result.getInt("studentid"));
                //  System.out.println("学号:" + result.getInt("studentid") + "姓名:" + result.getString("studentname"));
                //System.out.println(result.getInt("stu_id"));
                // System.out.println(result.getInt("user_cardid"));
                System.out.println(result.getInt("user_cardid") + "     " + result.getInt("user_cardpwd"));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭!！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取用户所有信息
     *
     * @param userId 用户ID
     * @return 返回Object[] 包括用户的所有信息
     */
    //获取用户所有信息
    public Object[] selectUserAll(int userId) {

        Object[] userAll = new Object[8];
        String userIdStr = (new Integer(userId)).toString();
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:orcl";
            String user = "hp";
            String password = "123456";
            con = DriverManager.getConnection(url, user, password); // 获取连接
            System.out.println("连接成功！");
            String sql = "select * from BANKUSER WHERE user_cardid=?"; // 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql); // 实例化预编译语句
            pre.setString(1, userIdStr); // 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            result = pre.executeQuery(); // 执行查询，括号中不需要再加参数
            while (result.next()) {
                userAll[0] = result.getInt("user_cardid");
                userAll[1] = result.getInt("user_cardpwd");
                userAll[2] = result.getString("user_name");
                userAll[3] = result.getString("user_sex");
                userAll[4] = result.getDate("user_birthday");
                userAll[5] = result.getString("user_phone");
                userAll[6] = result.getDate("user_regdate");
                userAll[7] = result.getDouble("user_money");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭!！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userAll;
    }

    //封装用户信息到用户对象
    public void setUser(User user) {
        Object[] userInfo = this.selectUserAll(user.getCardId());
        user.setCardPwd((int) userInfo[1]); //密码
        user.setUserName((String) userInfo[2]);
        user.setUserSex((String) userInfo[3]);
        user.setUserBirthday((Date) userInfo[4]);
        user.setUserPhone((String) userInfo[5]);
        user.setUserRegDate((Date) userInfo[6]);
        user.setUserMoney((Double) userInfo[7]);
    }
}
